#!/bin/bash



if [ -n "$1" ] ; then

  data_date=$1

else

  data_date=`date -d "-1 days" +%F`

fi



sql="
drop table if exists jtp_app_dwd.tmp_event_log_cleaned;
create table if not exists jtp_app_dwd.tmp_event_log_cleaned as

select *

from jtp_app_ods.ods_event_app_log

where dt = '${data_date}'

and deviceid is not null and length(deviceid) != 0

and eventid is not null and length(eventid) != 0

and sessionid is not null and length(sessionid) != 0

and properties is not null and size(properties) != 0;


drop table if exists jtp_app_dwd.tmp_event_log_session;
create table if not exists jtp_app_dwd.tmp_event_log_session as

with t1 as (

    select *,

           (\`timestamp\` - lag(\`timestamp\`,1,0) over (partition by deviceid order by \`timestamp\`)) /1000/60 as diff_ts

    from jtp_app_dwd.tmp_event_log_cleaned

), t2 as (

    select

        *,

        if(diff_ts >= 10,concat(deviceid,'_',\`timestamp\`),null) as start_sessionid

    from t1

), t3 as (

    select

        *,

        first_value(start_sessionid) over (partition by deviceid order by \`timestamp\`) as new_sessionid

    from t2

)

select

    account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude,

    nettype, osname, osversion, properties, releasechannel, resolution, sessionid, \`timestamp\`,

    new_sessionid

from t3;


use jtp_app_dwd;
drop function if exists aaa;
add jar hdfs://node101:8020/warehouse/app_jars/jtp-log-warehouse-1.0-SNAPSHOT.jar;
create temporary function aaa as 'com.gh';


drop table if exists jtp_app_dwd.tmp_event_log_dic;
create table if not exists jtp_app_dwd.tmp_event_log_dic as

with t1 as (

    select *,

           aaa(t1.latitude,t1.longitude,6) geohash

    from jtp_app_dwd.tmp_event_log_session t1

), t2 as (

    select * from jtp_app_dim.dim_md_areas where dt = '${data_date}'

)

select

    account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude,

    nettype, osname, osversion, properties, releasechannel, resolution, sessionid,

    \`timestamp\`, new_sessionid, geohash,province, city, district, street

from t1

left join t2 on t1.geohash = t2.geo_hash

where province is not null;



insert into table jtp_app_dwd.dwd_event_log_detail partition (dt = '${data_date}')

select * from jtp_app_dwd.tmp_event_log_dic;

"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${sql}"